package com.example.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

@Mapper
public interface StatisticsMapper {

    @Select("SELECT COUNT(*) FROM hotel")
    Integer getHotelCount();

    @Select("SELECT COALESCE(ROUND((COUNT(*) - " +
            "(SELECT COUNT(*) FROM hotel WHERE create_time < DATE_SUB(CURDATE(), INTERVAL 1 MONTH))) * 100.0 / " +
            "NULLIF((SELECT COUNT(*) FROM hotel WHERE create_time < DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), 0), 2), 0) " +
            "FROM hotel")
    Double getHotelIncreaseRate();

    @Select("SELECT COUNT(*) FROM booking WHERE DATE(booking_date) = CURDATE()")
    Integer getTodayBookingsCount();

    @Select("SELECT COALESCE(ROUND(((SELECT COUNT(*) FROM booking WHERE DATE(booking_date) = CURDATE()) - " +
            "(SELECT COUNT(*) FROM booking WHERE DATE(booking_date) = DATE_SUB(CURDATE(), INTERVAL 1 DAY))) * 100.0 / " +
            "NULLIF((SELECT COUNT(*) FROM booking WHERE DATE(booking_date) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)), 0), 2), 0)")
    Double getBookingTrend();

    @Select("SELECT COUNT(*) FROM user")
    Integer getUserCount();

    @Select("SELECT COUNT(*) FROM user WHERE create_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)")
    Integer getNewUsersThisWeek();

    @Select("SELECT COALESCE(SUM(total_price), 0) FROM booking WHERE status != 'CANCELLED'")
    Double getTotalRevenue();

    @Select("SELECT COALESCE(ROUND(((SELECT COALESCE(SUM(total_price), 0) FROM booking " +
            "WHERE status != 'CANCELLED' AND booking_date >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) - " +
            "(SELECT COALESCE(SUM(total_price), 0) FROM booking " +
            "WHERE status != 'CANCELLED' AND booking_date >= DATE_SUB(CURDATE(), INTERVAL 2 MONTH) " +
            "AND booking_date < DATE_SUB(CURDATE(), INTERVAL 1 MONTH))) * 100.0 / " +
            "NULLIF((SELECT COALESCE(SUM(total_price), 0) FROM booking " +
            "WHERE status != 'CANCELLED' AND booking_date >= DATE_SUB(CURDATE(), INTERVAL 2 MONTH) " +
            "AND booking_date < DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), 0), 2), 0)")
    Double getRevenueTrend();
    
    @Select("SELECT COUNT(*) FROM booking WHERE status != 'CANCELLED'")
    Integer getTotalBookingsCount();
    
    @Select("SELECT SUM(total_quantity) FROM room")
    Integer getTotalRoomsCount();
    
    @Select("SELECT COALESCE(ROUND((COUNT(*) * 100.0 / NULLIF((SELECT SUM(total_quantity) FROM room), 0)), 2), 0) " +
            "FROM booking WHERE status = 'RESERVED' " +
            "AND CURDATE() BETWEEN check_in_date AND check_out_date")
    Double getOccupancyRate();
    
    @Select("WITH RECURSIVE days AS (" +
            "  SELECT 0 as n " +
            "  UNION ALL " +
            "  SELECT n + 1 FROM days WHERE n < 6" +
            ") " +
            "SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL n DAY), '%m-%d') AS date " +
            "FROM days " +
            "ORDER BY n DESC")
    List<String> getLast7Days();
    
    @Select("WITH dates AS (" +
            "  SELECT DATE_SUB(CURDATE(), INTERVAL n DAY) as booking_day " +
            "  FROM (SELECT 0 as n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 " +
            "        UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) days" +
            ") " +
            "SELECT COALESCE(COUNT(b.id), 0) " +
            "FROM dates d " +
            "LEFT JOIN booking b ON DATE(b.booking_date) = d.booking_day " +
            "GROUP BY d.booking_day " +
            "ORDER BY d.booking_day")
    List<Integer> getBookingsCountByLast7Days();
    
    @Select("WITH RECURSIVE days AS (" +
            "  SELECT 0 as n " +
            "  UNION ALL " +
            "  SELECT n + 1 FROM days WHERE n < 29" +
            ") " +
            "SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL n DAY), '%m-%d') AS date " +
            "FROM days " +
            "ORDER BY n DESC")
    List<String> getLast30Days();
    
    @Select("WITH dates AS (" +
            "  SELECT DATE_SUB(CURDATE(), INTERVAL n DAY) as booking_day " +
            "  FROM (SELECT n FROM (SELECT 0 as n UNION ALL SELECT n + 1 FROM (SELECT 0 as n) t0 WHERE n < 29) t1) days" +
            ") " +
            "SELECT COALESCE(COUNT(b.id), 0) " +
            "FROM dates d " +
            "LEFT JOIN booking b ON DATE(b.booking_date) = d.booking_day " +
            "GROUP BY d.booking_day " +
            "ORDER BY d.booking_day")
    List<Integer> getBookingsCountByLast30Days();

    @Select("SELECT r.room_type as name, " +
            "COUNT(DISTINCT CASE WHEN b.status != 'CANCELLED' THEN b.id END) as value " +
            "FROM room r " +
            "LEFT JOIN booking b ON r.id = b.room_id " +
            "GROUP BY r.room_type " +
            "ORDER BY value DESC")
    List<Map<String, Object>> getRoomTypeDistribution();
    
    @Select("SELECT h.name " +
            "FROM hotel h " +
            "LEFT JOIN booking b ON h.id = b.hotel_id AND b.status != 'CANCELLED' " +
            "GROUP BY h.id, h.name " +
            "ORDER BY COUNT(b.id) DESC " +
            "LIMIT 10")
    List<String> getTopHotelsName();
    
    @Select("SELECT COUNT(b.id) " +
            "FROM hotel h " +
            "LEFT JOIN booking b ON h.id = b.hotel_id AND b.status != 'CANCELLED' " +
            "GROUP BY h.id " +
            "ORDER BY COUNT(b.id) DESC " +
            "LIMIT 10")
    List<Integer> getTopHotelsBookingCount();
} 